const express = require('express');
const router = express.Router();
const { pool } = require('../config/db');
const auth = require('../middleware/auth');

// 获取学生成绩
router.get('/student', auth, async (req, res) => {
  try {
    const studentId = req.user.id;  // 使用 req.user.id 替代 userId
    const semester = req.query.semester || '';
    
    console.log('获取学生成绩，用户ID:', studentId, '学期:', semester);
    
    let query = `
      SELECT 
        g.id,
        c.name AS courseName,
        c.courseCode,
        c.credits AS credit,
        g.score,
        g.gpa,
        u.name AS teacher,
        '考试' AS examType,
        c.semester,
        g.comment AS scoreComment,
        g.createdAt AS gradedAt,
        g.updatedAt AS lastUpdated,
        cl.className AS className
      FROM 
        grades g
      JOIN 
        courses c ON g.courseId = c.id
      LEFT JOIN 
        users u ON c.teacherId = u.id
      LEFT JOIN 
        course_class_relation ccr ON c.id = ccr.courseId
      LEFT JOIN 
        classes cl ON ccr.classId = cl.id
      WHERE 
        g.studentId = ?
    `;
    
    const queryParams = [studentId];  // 使用 studentId 替代 userId
    
    // 如果提供了学期参数，则添加到查询条件中
    if (semester && semester.trim() !== '') {
      // 打印学期参数，帮助调试
      console.log('查询学期:', semester);
      
      // 尝试不同的学期格式匹配
      query += ` AND (c.semester = ? OR c.semester LIKE ? OR c.semester LIKE ?)`;
      
      // 处理可能的学期格式：2024-2025学年第一学期 -> 2024-2025-1
      const semesterParts = semester.match(/(\d{4}-\d{4}).*?([一二三四])/);
      let formattedSemester = semester;
      
      if (semesterParts) {
        const yearRange = semesterParts[1];
        const termNumber = {'一': 1, '二': 2, '三': 3, '四': 4}[semesterParts[2]];
        formattedSemester = `${yearRange}-${termNumber}`;
        console.log('格式化后的学期:', formattedSemester);
      }
      
      queryParams.push(semester);
      queryParams.push(`%${semester}%`);
      queryParams.push(`%${formattedSemester}%`);
    }
    
    console.log('执行SQL查询:', query);
    console.log('查询参数:', queryParams);
    
    const [rows] = await pool.query(query, queryParams);
    console.log('查询结果行数:', rows.length);
    
    // 如果没有找到记录，尝试不带学期条件查询
    if (rows.length === 0 && semester) {
      console.log('使用学期条件未找到记录，尝试不带学期条件查询');
      
      const baseQuery = `
        SELECT 
          g.id,
          c.name AS courseName,
          c.courseCode,
          c.credits AS credit,
          g.score,
          g.gpa,
          u.name AS teacher,
          '考试' AS examType,
          c.semester,
          g.comment AS scoreComment,
          g.createdAt AS gradedAt,
          g.updatedAt AS lastUpdated,
          cl.className AS className
        FROM 
          grades g
        JOIN 
          courses c ON g.courseId = c.id
        LEFT JOIN 
          users u ON c.teacherId = u.id
        LEFT JOIN 
          course_class_relation ccr ON c.id = ccr.courseId
        LEFT JOIN 
          classes cl ON ccr.classId = cl.id
        WHERE 
          g.studentId = ?
      `;
      
      const [allRows] = await pool.query(baseQuery, [studentId]);  // 使用 studentId
      console.log('不带学期条件查询结果行数:', allRows.length);
      
      res.json({ grades: allRows });
    } else {
      res.json({ grades: rows });
    }
  } catch (error) {
    console.error('获取学生成绩失败:', error);
    res.status(500).json({ message: '服务器错误', error: error.message });
  }
});

// 获取教师课程及成绩统计
router.get('/teacher/courses', auth, async (req, res) => {
  try {
    const teacherId = req.user.id;
    const semester = req.query.semester || '';
    
    console.log('获取教师课程成绩，教师ID:', teacherId, '学期:', semester);
    
    let query = `
      SELECT 
        c.id,
        c.name AS courseName,
        cl.name AS className,
        COUNT(DISTINCT csr.studentId) AS studentCount,
        COUNT(DISTINCT g.studentId) AS gradedCount,
        CASE 
          WHEN COUNT(DISTINCT csr.studentId) = COUNT(DISTINCT g.studentId) THEN '已完成'
          WHEN COUNT(DISTINCT g.studentId) > 0 THEN '进行中'
          ELSE '未开始'
        END AS status,
        IFNULL(AVG(g.score), 0) AS averageScore,
        IFNULL(
          (SUM(CASE WHEN g.score >= 60 THEN 1 ELSE 0 END) / 
          NULLIF(COUNT(g.id), 0)) * 100, 
          0
        ) AS passRate
      FROM 
        courses c
      LEFT JOIN 
        classes cl ON c.classId = cl.id
      LEFT JOIN 
        course_student_relation csr ON c.id = csr.courseId
      LEFT JOIN 
        grades g ON c.id = g.courseId AND csr.studentId = g.studentId
      WHERE 
        c.teacherId = ?
    `;
    
    const queryParams = [teacherId];
    
    if (semester) {
      query += ' AND c.semester = ?';
      queryParams.push(semester);
    }
    
    query += ' GROUP BY c.id, c.name, cl.name';
    
    const [rows] = await pool.query(query, queryParams);
    
    // 计算统计数据
    const totalCourses = rows.length;
    const completedCourses = rows.filter(course => course.status === '已完成').length;
    const pendingCourses = totalCourses - completedCourses;
    const averageScore = rows.reduce((sum, course) => sum + parseFloat(course.averageScore), 0) / (totalCourses || 1);
    
    const stats = {
      totalCourses,
      completedCourses,
      pendingCourses,
      averageScore: averageScore.toFixed(1)
    };
    
    res.json({ 
      courses: rows,
      stats: stats
    });
  } catch (error) {
    console.error('获取教师课程成绩失败:', error);
    res.status(500).json({ message: '服务器错误', error: error.message });
  }
});

// 获取辅导员班级课程成绩
router.get('/counselor/courses', auth, async (req, res) => {
  try {
    const classId = req.query.classId;
    const semester = req.query.semester || '';
    
    if (!classId) {
      return res.status(400).json({ message: '缺少班级ID参数' });
    }
    
    console.log('获取班级课程成绩，班级ID:', classId, '学期:', semester);
    
    let query = `
      SELECT 
        c.id,
        c.name,
        u.name AS teacher,
        c.credits AS credit,
        COUNT(DISTINCT csr.studentId) AS studentCount,
        IFNULL(AVG(g.score), 0) AS averageScore,
        IFNULL(
          (SUM(CASE WHEN g.score >= 60 THEN 1 ELSE 0 END) / 
          NULLIF(COUNT(g.id), 0)) * 100, 
          0
        ) AS passRate,
        IFNULL(
          (SUM(CASE WHEN g.score >= 85 THEN 1 ELSE 0 END) / 
          NULLIF(COUNT(g.id), 0)) * 100, 
          0
        ) AS excellentRate,
        SUM(CASE WHEN g.score < 60 THEN 1 ELSE 0 END) AS failCount,
        CASE 
          WHEN COUNT(DISTINCT g.studentId) = COUNT(DISTINCT csr.studentId) THEN '已完成'
          WHEN COUNT(DISTINCT g.studentId) > 0 THEN '进行中'
          ELSE '未开始'
        END AS status
      FROM 
        courses c
      LEFT JOIN 
        users u ON c.teacherId = u.id
      LEFT JOIN 
        course_class_relation ccr ON c.id = ccr.courseId
      LEFT JOIN 
        course_student_relation csr ON c.id = csr.courseId
      LEFT JOIN 
        grades g ON c.id = g.courseId AND csr.studentId = g.studentId
      WHERE 
        ccr.classId = ?
    `;
    
    const queryParams = [classId];
    
    if (semester) {
      query += ' AND c.semester = ?';
      queryParams.push(semester);
    }
    
    query += ' GROUP BY c.id, c.name, u.name, c.credits';
    
    const [rows] = await pool.query(query, queryParams);
    
    // 计算班级整体统计数据
    const totalStudents = rows.reduce((sum, course) => sum + parseInt(course.studentCount), 0);
    const averageScore = rows.reduce((sum, course) => sum + parseFloat(course.averageScore) * parseInt(course.studentCount), 0) / (totalStudents || 1);
    const totalGrades = rows.reduce((sum, course) => sum + parseInt(course.studentCount), 0);
    const passCount = rows.reduce((sum, course) => sum + (parseFloat(course.passRate) / 100) * parseInt(course.studentCount), 0);
    const excellentCount = rows.reduce((sum, course) => sum + (parseFloat(course.excellentRate) / 100) * parseInt(course.studentCount), 0);
    const failCount = rows.reduce((sum, course) => sum + parseInt(course.failCount), 0);
    
    const stats = {
      averageScore: averageScore.toFixed(1),
      passRate: (passCount / (totalGrades || 1) * 100).toFixed(1),
      excellentRate: (excellentCount / (totalGrades || 1) * 100).toFixed(1),
      failCount: failCount
    };
    
    res.json({ 
      courses: rows,
      stats: stats
    });
  } catch (error) {
    console.error('获取班级课程成绩失败:', error);
    res.status(500).json({ message: '服务器错误', error: error.message });
  }
});

// 获取课程学生成绩详情
router.get('/course/:courseId', auth, async (req, res) => {
  try {
    const { courseId } = req.params;
    const teacherId = req.user.id;
    
    // 验证该课程是否属于该教师
    const [courseRows] = await pool.query(
      'SELECT * FROM courses WHERE id = ? AND teacherId = ?',
      [courseId, teacherId]
    );
    
    if (courseRows.length === 0) {
      return res.status(403).json({ message: '无权访问该课程成绩' });
    }
    
    // 获取该课程的所有学生成绩
    const [rows] = await pool.query(`
      SELECT 
        u.id AS studentId,
        u.userId AS studentNumber,
        u.name AS studentName,
        g.score,
        g.gpa,
        g.comment
      FROM 
        course_student_relation csr
      JOIN 
        users u ON csr.studentId = u.id
      LEFT JOIN 
        grades g ON csr.courseId = g.courseId AND csr.studentId = g.studentId
      WHERE 
        csr.courseId = ?
      ORDER BY 
        u.name
    `, [courseId]);
    
    res.json({ students: rows });
  } catch (error) {
    console.error('获取课程学生成绩失败:', error);
    res.status(500).json({ message: '服务器错误', error: error.message });
  }
});

// 录入/更新学生成绩
router.post('/update', auth, async (req, res) => {
  try {
    const { courseId, studentId, score, comment } = req.body;
    const teacherId = req.user.id;
    
    if (!courseId || !studentId || score === undefined) {
      return res.status(400).json({ message: '缺少必要参数' });
    }
    
    // 验证该课程是否属于该教师
    const [courseRows] = await pool.query(
      'SELECT * FROM courses WHERE id = ? AND teacherId = ?',
      [courseId, teacherId]
    );
    
    if (courseRows.length === 0) {
      return res.status(403).json({ message: '无权录入该课程成绩' });
    }
    
    // 计算GPA
    let gpa = 0;
    if (score >= 90) gpa = 4.0;
    else if (score >= 85) gpa = 3.7;
    else if (score >= 80) gpa = 3.3;
    else if (score >= 75) gpa = 3.0;
    else if (score >= 70) gpa = 2.7;
    else if (score >= 65) gpa = 2.3;
    else if (score >= 60) gpa = 2.0;
    else gpa = 0;
    
    // 检查是否已存在成绩记录
    const [existingRows] = await pool.query(
      'SELECT * FROM grades WHERE courseId = ? AND studentId = ?',
      [courseId, studentId]
    );
    
    if (existingRows.length > 0) {
      // 更新现有成绩
      await pool.query(
        'UPDATE grades SET score = ?, gpa = ?, comment = ?, updatedAt = NOW() WHERE courseId = ? AND studentId = ?',
        [score, gpa, comment || '', courseId, studentId]
      );
    } else {
      // 创建新成绩记录
      await pool.query(
        'INSERT INTO grades (courseId, studentId, score, gpa, comment, createdAt, updatedAt) VALUES (?, ?, ?, ?, ?, NOW(), NOW())',
        [courseId, studentId, score, gpa, comment || '']
      );
    }
    
    res.json({ message: '成绩录入成功' });
  } catch (error) {
    console.error('成绩录入失败:', error);
    res.status(500).json({ message: '服务器错误', error: error.message });
  }
});

module.exports = router;